import logging
import os
import re
import sqlite3


class DBCon:
    def __init__(self):
        self.db_con = None
        self.db_file = None
        self.last_error_desc = ""

    def connect(self):
        self.db_file = os.path.abspath("../crud_api/crud.db")
        self.last_error_desc = None
        try:
            self.db_con = sqlite3.connect(self.db_file, isolation_level=None, check_same_thread=False)
            self.db_con.execute("PRAGMA synchronous=normal")
            self.db_con.execute("PRAGMA journal_mode=WAL")
            logging.info(f"connect sqlite3[%s] success" % self.db_file)
            return True
        except Exception as e:
            self.last_error_desc = str(e)
            logging.error(f"connect sqlite3[%s] failed [%s]" % (self.db_file, e))
            return False

    def close(self):
        self.last_error_desc = None
        if self.db_con:
            logging.info(f"close sqlite3[%s]" % self.db_file)
            self.db_con.close()
        self.db_con = None

    def get_last_error(self):
        return self.last_error_desc

    def clear_last_error(self):
        self.last_error_desc = None

    def fetchone(self, sql, params=None):
        db_cur = None
        row = None
        try:
            logging.info(f"%s" % re.sub(r"\s+", " ", sql))
            db_cur = self.db_con.cursor()
            if params:
                logging.info(params)
                db_cur.execute(sql, params)
            else:
                db_cur.execute(sql)
            row = db_cur.fetchone()

        except Exception as e:
            self.last_error_desc = str(e)
            logging.error(f"Exception %s, %s" % (re.sub(r"\s+", " ", sql), e))
        finally:
            if db_cur:
                db_cur.close()
        return row

    def fetchall(self, sql, params=None):
        db_cur = None
        rows = []
        try:
            logging.info(f"%s" % re.sub(r"\s+", " ", sql))

            db_cur = self.db_con.cursor()
            if params:
                logging.info(params)
                db_cur.execute(sql, params)
            else:
                db_cur.execute(sql)
            rows = db_cur.fetchall()

        except Exception as e:
            self.last_error_desc = str(e)
            logging.error(f"Exception %s, %s" % (re.sub(r"\s+", " ", sql), e))
        finally:
            if db_cur:
                db_cur.close()
        return rows

    def fetchall_dict(self, sql, params=None):
        db_cur = None
        rows = []
        try:
            logging.info(f"%s" % re.sub(r"\s+", " ", sql))
            db_cur = self.db_con.cursor()
            if params:
                logging.info(params)
                db_cur.execute(sql, params)
            else:
                db_cur.execute(sql)
            columns = [col[0] for col in db_cur.description]
            rows = [dict(zip(columns, row)) for row in db_cur.fetchall()]

        except Exception as e:
            self.last_error_desc = str(e)
            logging.error(f"Exception: {e}")
        finally:
            if db_cur:
                db_cur.close()
        return rows

    def execute_update(self, sql, params=None):
        db_cur = None
        ret = False
        try:
            logging.info(f"%s" % re.sub(r"\s+", " ", sql))
            db_cur = self.db_con.cursor()
            if params:
                logging.info(params)
                db_cur.execute(sql, params)
            else:
                db_cur.execute(sql)
            self.db_con.commit()
            ret = True
        except Exception as e:
            self.last_error_desc = str(e)
            logging.error(f"Exception %s" % e)
        finally:
            if db_cur:
                db_cur.close()
        return ret

    # 分页查询,
    def execute_page(self, sql, page_index=1, page_size=10, params=None):
        # 查询总条数
        count_sql = f"""select count(*) as total_count from({sql})TT"""
        row = self.fetchone(count_sql, params)
        if row:
            total_count = row[0]
        else:
            total_count = 0

        total_page = int(total_count / page_size)
        if total_count > 0:
            total_page += 1

        begin_row = (page_index * page_size) - page_size
        # 查询分页数据

        sql_paginated = """
            select tt.*
            from (
                %s
            ) tt limit %s,%s
        """ % (sql, begin_row, page_size)

        index = begin_row + 1
        items = self.fetchall_dict(sql_paginated, params)
        for item in items:
            item["_index"] = index
            index += 1

        res = {"total": total_count, "page": total_page, "items": items}
        return res



dbCon = None


def fetchone(sql, params=None):
    global dbCon
    if dbCon is None:
        dbCon = DBCon()
        dbCon.connect()
    return dbCon.fetchone(sql, params)


def fetchall(sql, params=None):
    global dbCon
    if dbCon is None:
        dbCon = DBCon()
        dbCon.connect()
    return dbCon.fetchall(sql, params)


def fetchall_dict(sql, params=None):
    global dbCon
    if dbCon is None:
        dbCon = DBCon()
        dbCon.connect()
    return dbCon.fetchall_dict(sql, params)


def execute_update(sql, params=None):
    global dbCon
    if dbCon is None:
        dbCon = DBCon()
        dbCon.connect()
    return dbCon.execute_update(sql, params)


def execute_page(sql, page_index=1, page_size=10, params=None):
    global dbCon
    if dbCon is None:
        dbCon = DBCon()
        dbCon.connect()
    return dbCon.execute_page(sql, page_index, page_size, params)


def get_last_error():
    global dbCon
    if dbCon is None:
        dbCon = DBCon()
        dbCon.connect()
    return dbCon.get_last_error()
